<?php
// public/reports.php
require_once __DIR__ . '/../includes/auth.php';
require_once __DIR__ . '/../includes/db.php';
require_login();

$title = "Reports | JO & COS RMS";

// Determine report type
$reportType = $_GET['type'] ?? 'employees';

// Fetch data depending on report type
if ($reportType === 'documents') {
    $stmt = $pdo->query("
        SELECT d.id,
               e.full_name,
               d.doc_type,
               d.file_path,
               d.remarks,
               d.uploaded_at,
               d.created_at
        FROM documents d
        JOIN employees e ON d.employee_id = e.id
        ORDER BY d.created_at DESC
    ");
    $records = $stmt->fetchAll(PDO::FETCH_ASSOC);
} else {
    $stmt = $pdo->query("SELECT id, name, email, role, status, created_at 
                         FROM users 
                         ORDER BY created_at DESC");
    $records = $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// Handle export (unchanged)
if (isset($_GET['export'])) {
    $type = $_GET['export'];
    $filename = $reportType . "_report";

    if ($type === 'csv') {
        header('Content-Type: text/csv');
        header("Content-Disposition: attachment; filename=\"{$filename}.csv\"");
        $out = fopen('php://output', 'w');

        if ($reportType === 'documents') {
            fputcsv($out, array_keys($records[0] ?? []));
            foreach ($records as $r) {
                fputcsv($out, $r);
            }
        } else {
            fputcsv($out, array_keys($records[0] ?? []));
            foreach ($records as $r) {
                fputcsv($out, $r);
            }
        }
        fclose($out);
        exit;
    }

    if ($type === 'excel') {
        require_once __DIR__ . '/../vendor/autoload.php';
        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        if (!empty($records)) {
            $sheet->fromArray(array_keys($records[0]), NULL, 'A1');
            $row = 2;
            foreach ($records as $r) {
                $sheet->fromArray(array_values($r), NULL, "A{$row}");
                $row++;
            }
        }
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header("Content-Disposition: attachment; filename=\"{$filename}.xlsx\"");
        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        $writer->save('php://output');
        exit;
    }

    if ($type === 'pdf') {
        require_once __DIR__ . '/../vendor/autoload.php';
        $pdf = new \TCPDF();
        $pdf->AddPage();
        $html = "<h2>".ucfirst($reportType)." Report</h2><table border='1' cellpadding='4'><tr>";
        if (!empty($records)) {
            foreach (array_keys($records[0]) as $h) {
                $html .= "<th>".htmlspecialchars($h)."</th>";
            }
            $html .= "</tr>";
            foreach ($records as $r) {
                $html .= "<tr>";
                foreach ($r as $val) {
                    $html .= "<td>".htmlspecialchars((string)$val)."</td>";
                }
                $html .= "</tr>";
            }
        }
        $html .= "</table>";
        $pdf->writeHTML($html);
        $pdf->Output("{$filename}.pdf", 'D');
        exit;
    }
}
$empQuery = $pdo->query("SELECT * FROM employees ORDER BY full_name ASC");
$employees = $empQuery->fetchAll(PDO::FETCH_ASSOC);

include __DIR__ . '/../includes/header.php';
include __DIR__ . '/../includes/sidebar.php';
?>
<style>
 @media print {
          .btn-print {
            display:none !important;
          }
      }
	  
	  #table1, #table2, #table3, #table4 {
    display: none;
	  }
	#label1, #label2, #label3, #label4 {
    display: none;
}
</style>






<section class="content-header">
  <div class="container-fluid d-flex justify-content-between align-items-center">
    <h1>Reports</h1>
    <select>
		<option value="">Select</option>
	<option value="all">ALL</option>
	<option value="table1">COS NON-TEACHING</option>
	<option value="table2">JOB ORDER NON-TEACHING</option>
	<option value="table3">COS PART-TIME INSTRUCTOR</option>
	<option value="table4">COS TEACHING</option>
	
	
	</select>
  </div>
</section>
 <a class = "btn btn-success btn-print" href = "" onclick = "window.print()"><i class ="glyphicon glyphicon-print"></i> Print</a>
<section class="content">
  <div class="card">
    <div class="card-body">
    
	  <div style="margin-top:30px;">


     </div> <!-- Responsive wrapper -->
      <div class="table-responsive" style="text-align:center;">
	<table class="header">
    <tr>
        <td><img src="http://localhost/jo_cos_system/super/l.png" alt="Logo" style="width:90px;height:90px;border-raduis:50%;" class="brand-image img-circle elevation-3"></td>
        <td style="width: 70%;">
            Republic of the Philippines<br>
            <strong>DR. EMILIO B. ESPINOSA SR., MEMORIAL STATE COLLEGE OF AGRICULTURE AND TECHNOLOGY</strong><br>
            Cabitan, Mandaon, Masbate | <a href="http://www.debesmscat.edu.ph">www.debesmscat.edu.ph</a><br>
            
        </td>
        
        <td style="width: 15%;">
            
            <img src="http://localhost/jo_cos_system/super/r.png" alt="mylogo" style="width:90px;height:90px;">
        </td>
    </tr>
</table>
		 
		 <h5>CHECKLIST OF DEBESMSCAT COS/JO PERSONNEL AS OF DATE _<input type="date"/> </h5>
		 
		 
		 <br></br>
		  <br></br>
		<label id="label1">COS NON-TEACHING</label>
<table id="table1" class="table table-bordered table-striped nowrap">

    <thead>
        <tr>
            <th>No</th>
            <th>Name</th>
            <th>Position</th>
            <th>Office</th>
        </tr>
    </thead>
    <tbody>
	<?php
	$con = mysqli_connect("127.0.0.1","root","","mydatabase");
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }
  date_default_timezone_set("Asia/Manila");
	$query1=mysqli_query($con,"select * from employees where employment_type='COS Non-Teaching'")or die(mysqli_error($con));
	while($row1=mysqli_fetch_array($query1)){
          
	?>
		<tr>
		    <td><?php echo $row1['id'];?></td>
			 <td><?php echo $row1['full_name'];?> </td>
			  <td><?php echo $row1['job_title'];?></td>
	<td><?php echo $row1['college_office']; }?></td>
		</tr>
		 
        </tbody>
      </table>
	  
	  
		  <label id="label2">JOB ORDER NON-TEACHING</label>
<table id="table2" class="table table-bordered table-striped nowrap">

    <thead>
        <tr>
            <th>No</th>
            <th>Name</th>
            <th>Position</th>
            <th>Office</th>
        </tr>
    </thead>
    <tbody>
		<?php
	$con = mysqli_connect("127.0.0.1","root","","mydatabase");
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }
  date_default_timezone_set("Asia/Manila");
	$query1=mysqli_query($con,"select * from employees where employment_type='JO Non-Teaching'")or die(mysqli_error($con));
	while($row1=mysqli_fetch_array($query1)){
          
	?>
		<tr>
		    <td><?php echo $row1['id'];?></td>
			 <td><?php echo $row1['full_name'];?> </td>
			  <td><?php echo $row1['job_title'];?></td>
	<td><?php echo $row1['college_office']; }?></td>
		</tr>
		 
        </tbody>
      </table>
	  

		 <label id="label3">COS PART-TIME INSTRUCTOR</label>
<table id="table3" class="table table-bordered table-striped nowrap">

    <thead>
        <tr>
            <th>No</th>
            <th>Name</th>
            <th>Position</th>
            <th>Office</th>
        </tr>
    </thead>
    <tbody>
		<?php
	$con = mysqli_connect("127.0.0.1","root","","mydatabase");
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }
  date_default_timezone_set("Asia/Manila");
	$query1=mysqli_query($con,"select * from employees where employment_type='Partime-Teaching'")or die(mysqli_error($con));
	while($row1=mysqli_fetch_array($query1)){
          
	?>
		<tr>
		    <td><?php echo $row1['id'];?></td>
			 <td><?php echo $row1['full_name'];?> </td>
			  <td><?php echo $row1['job_title'];?></td>
	<td><?php echo $row1['college_office']; }?></td>
		</tr>
		 
        </tbody>
      </table>

		<label id="label4">COS TEACHING</label>
<table id="table4" class="table table-bordered table-striped nowrap">

    <thead>
        <tr>
            <th>No</th>
            <th>Name</th>
            <th>Position</th>
            <th>Office</th>
        </tr>
    </thead>
    <tbody>
				<?php
	$con = mysqli_connect("127.0.0.1","root","","mydatabase");
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }
  date_default_timezone_set("Asia/Manila");
	$query1=mysqli_query($con,"select * from employees where employment_type='COS Teaching'")or die(mysqli_error($con));
	while($row1=mysqli_fetch_array($query1)){
          
	?>
		<tr>
		    <td><?php echo $row1['id'];?></td>
			 <td><?php echo $row1['full_name'];?> </td>
			  <td><?php echo $row1['job_title'];?></td>
	<td><?php echo $row1['college_office']; }?></td>
		</tr>
		 
        </tbody>
      </table>
      </div>
     

    </div>
  </div>
</section>

<script>
document.addEventListener('DOMContentLoaded', function () {
    const selector = document.querySelector('select'); 

    // Tables and their corresponding labels
    const sections = [
        { table: "table1", label: "label1" },
        { table: "table2", label: "label2" },
        { table: "table3", label: "label3" },
        { table: "table4", label: "label4" }
    ];

    selector.addEventListener('change', function () {
        let selected = this.value;

        if (selected === 'all') {
            // Show everything
            sections.forEach(sec => {
                document.getElementById(sec.table).style.display = "table";
                document.getElementById(sec.label).style.display = "block";
            });
        } else {
            // Hide all
            sections.forEach(sec => {
                document.getElementById(sec.table).style.display = "none";
                document.getElementById(sec.label).style.display = "none";
            });

            // Show selected
            const chosen = sections.find(sec => sec.table === selected);
            document.getElementById(chosen.table).style.display = "table";
            document.getElementById(chosen.label).style.display = "block";
        }
    });
});
</script>

<?php include __DIR__ . '/../includes/footer.php'; ?>

<!-- DataTables -->
<script src="https://cdn.datatables.net/1.13.6/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.13.6/js/dataTables.bootstrap4.min.js"></script>
<script src="https://cdn.datatables.net/responsive/2.5.0/js/dataTables.responsive.min.js"></script>
<link rel="stylesheet" href="https://cdn.datatables.net/1.13.6/css/dataTables.bootstrap4.min.css"/>
<link rel="stylesheet" href="https://cdn.datatables.net/responsive/2.5.0/css/responsive.bootstrap4.min.css"/>
